
package com.productmodule.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import util.ConnectDB;
import util.UtilDAO;
import util.VskPage;

import com.productmodule.bean.TbProduct;
import com.productmodule.bean.TbProductPrice;
public class TbProductDAO {
    private Connection conn = null;
    private PreparedStatement pt = null;
    private ResultSet rs = null;
    private boolean flag = false;
    private String sql = "";
    private TbProduct tbProduct=null;
    private List<TbProduct> tbProductList=null;

    public boolean add(TbProduct inTbProduct){
        try{
            
        conn=ConnectDB.getSqlServerConnection();
        sql="insert into Tb_Product(ProductTypeId,ProductName,ProductMemo,UserId) values(?,?,?,?)";
        pt = conn.prepareStatement(sql);
        
        pt.setInt(1,inTbProduct.getProductTypeId());
        pt.setString(2,inTbProduct.getProductName());
        pt.setString(3,inTbProduct.getProductMemo());
        pt.setInt(4,inTbProduct.getUserId());
        if (pt.executeUpdate() > 0) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return flag;
    }
    public boolean del(int inProductId){
        return UtilDAO.del("Tb_Product", "ProductId", inProductId);
    }
    public boolean edit(TbProduct inTbProduct){
        try{
            
        conn=ConnectDB.getSqlServerConnection();
        sql="update Tb_Product set ProductTypeId=?,ProductName=?,ProductMemo=?,UserId=?,CreateTime=getdate() where ProductId=?";
        pt = conn.prepareStatement(sql);
        
        pt.setInt(1,inTbProduct.getProductTypeId());
        pt.setString(2,inTbProduct.getProductName());
        pt.setString(3,inTbProduct.getProductMemo());
        pt.setInt(4,inTbProduct.getUserId());
        pt.setInt(5,inTbProduct.getProductId());
        if (pt.executeUpdate() > 0) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return flag;
    }
    public TbProduct getTbProductByProductId(int inProductId) {
        tbProduct=null;
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_Product where ProductId=? ";
            pt = conn.prepareStatement(sql);
            pt.setInt(1,inProductId);
            rs = pt.executeQuery();
            while (rs.next()) {
                tbProduct = getTbProduct(rs);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbProduct;
    }
    public List<TbProduct> getTbProductAll() {
        tbProductList=new ArrayList<TbProduct>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_Product";
            pt = conn.prepareStatement(sql);
            rs = pt.executeQuery();
            while (rs.next()) {
                tbProductList.add(getTbProduct(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbProductList;
    }
    public List<TbProduct> getTbProductAll(VskPage vp) {
        tbProductList=new ArrayList<TbProduct>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_Product";
            vp.setAllCountSql(sql);
            vp.getAllRecordCount(conn, vp.getAllCountSql());
            pt = conn.prepareStatement(vp.processPageSql( "Tb_Product", "ProductId"));
            rs = pt.executeQuery();
            while (rs.next()) {
                tbProductList.add(getTbProduct(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
                        ConnectDB.closeSqlConnection();        }
        return tbProductList;
    }
   
    /**
     * get the maxId of the Product
     *@author qingyu zhang
     *@function:
     * @return
     *2011-6-22
     *下午04:07:10
     *ProjectManagerT
     *com.productmodule.dao
     *int
     */
   public int getMaxId(){
	   int maxId = 0;
	   try {
		conn = ConnectDB.getSqlServerConnection();
		sql="select max(ProductId) maxProductId from Tb_Product";
		pt = conn.prepareStatement(sql);
		rs = pt.executeQuery();
		if(rs.next()){
			maxId = rs.getInt("maxProductId");
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
        ConnectDB.closeSqlConnection();      
    return maxId;
	}
   }
   /**
    * 
    *@author qingyu zhang
    *@function:
    * @param productTypeId
    * @return
    *2011-6-26
    *上午11:06:43
    *ProjectManagerT
    *com.productmodule.dao
    *List<TbProduct>
    */
   public List<TbProduct> getProductByProductTypeId(int productTypeId){
	   tbProductList = new ArrayList<TbProduct>();
	   try {
		conn = ConnectDB.getSqlServerConnection();
		sql = "select * from Tb_Product where ProductTypeId="+productTypeId;
		pt = conn.prepareStatement(sql);
		rs = pt.executeQuery();
		while(rs.next()){
			tbProductList.add(this.getTbProduct(rs));
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
        ConnectDB.closeSqlConnection();      
    	}
	return tbProductList;
   }
   /**
    * get Products by companyId
    *@author qingyu zhang
    *@function:
    * @param companyId
    * @return
    *2011-7-2
    *下午04:03:09
    *ProjectManagerT
    *com.productmodule.dao
    *List<TbProduct>
    */
   public List<TbProduct> getProductByCompanyId(int companyId){
	   tbProductList = new ArrayList<TbProduct>();
	   try {
		conn = ConnectDB.getSqlServerConnection();
		sql = "select * from tb_Product where productId in (select ProductId from tb_productPrice where CompanyId="+companyId+") and ProductTypeId=0";
		pt = conn.prepareStatement(sql);
		rs = pt.executeQuery();
		while(rs.next()){
			tbProductList.add(this.getTbProduct(rs));
		}
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
        ConnectDB.closeSqlConnection();      
    	}
	return tbProductList;
   }
   
   public TbProduct getTbProduct(ResultSet rs) throws Exception{
       TbProduct tbProduct = new TbProduct();
       tbProduct.setProductId(rs.getInt("ProductId"));
       tbProduct.setProductTypeId(rs.getInt("ProductTypeId"));
       tbProduct.setProductName(rs.getString("ProductName"));
       tbProduct.setProductMemo(rs.getString("ProductMemo"));
       tbProduct.setUserId(rs.getInt("UserId"));
       tbProduct.setCreateTime(rs.getString("CreateTime"));
       return tbProduct;
   }
}
